--最近180天订单，去除回单、退回件
with tmp_mid_02 as (
    select * from (
        select
             t1.waybill_id
            ,max(coalesce(t1.real_pick_network_code,t1.pick_network_code,'0000'))  as network_code   --取件网点编码
            ,max(if(substr(t1.input_time,1,10)='{{ execution_date | cst_ds }}',substr(t1.input_time,1,10),null)) as input_date --t-1日订单
            ,max(if(substr(t1.input_time,1,10)<'{{ execution_date | cst_ds }}',substr(t1.input_time,1,10),null)) as input_date_2 --T-2之前的订单日期
        from (
             select
                 waybill_id,sign_receipt,real_pick_network_code,pick_network_code,input_time,dt
             from jms_ods.yl_oms_oms_order t1
             where t1.dt >= date_add('{{ execution_date | cst_ds }}', -181)
             and t1.dt <= '{{ execution_date | cst_ds }}'
             and substr(t1.input_time,1,10) >= date_add('{{ execution_date | cst_ds }}', -180)
             and substr(t1.input_time,1,10) <= '{{ execution_date | cst_ds }}'
             and t1.sign_receipt = 0     --过滤掉回单
             and t1.cancel_time is null  --过滤掉被取消的订单
             and t1.waybill_id like 'JT%'
        ) t1
        left join (
              select waybill_no
              from jms_dwd.dwd_tab_reback_transfer_express_base_dt
              where dt>=date_add('{{ execution_date | cst_ds }}',-181)
                and dt<='{{ execution_date | cst_ds }}'
              group by waybill_no
        ) t2
        on t1.waybill_id = t2.waybill_no
        where t2.waybill_no is null  --过滤掉退回的单子
        group by t1.waybill_id
    ) tmp where input_date is not null and input_date_2 is not null
)

--(t-30)已签收的运单产生新的轨迹
--此处的数据是前30天的数据
, tmp_mid_04 AS (
SELECT
     bill_code as waybill_id
    ,pick_network_code as network_code
    ,'{{ execution_date | cst_ds }}' AS input_date
FROM (
         select
                bill_code
               ,biz_sign_date
               ,latest_scan_date
               ,sign_date
               ,pick_network_code
               ,latest_scan_type
               ,if(
                   (substr(biz_sign_date,1,10)=date_add('{{ execution_date | cst_ds }}',-30) or substr(sign_date,1,10)=date_add('{{ execution_date | cst_ds }}',-30))
                    and latest_scan_date>greatest(biz_sign_date,sign_date)
                    and latest_scan_type in ('1','2','3','4','5','6','7'),1,0) as is_duplicate_flag
         from jms_dwd.dwd_wide_yl_rt_ssmx_bill_detail_hf
         where dt>=date_add('{{ execution_date | cst_ds }}',-50)
           and dt<=date_add('{{ execution_date | cst_ds }}',-29)
     ) wide where is_duplicate_flag=1
)

--网点信息数据
,tmp_result AS (
SELECT
     t1.waybill_id          as bill_code
    ,t1.network_code
    ,t2.name                as network_name
    ,t2.fran_code
    ,t2.fran_name
    ,t2.agent_code
    ,t2.agent_name
    ,t1.input_date
    ,t1.flag
FROM ( SELECT
             waybill_id
            ,network_code
            ,'{{ execution_date | cst_ds }}' as input_date
            ,1 as flag
        FROM tmp_mid_02
        UNION ALL
        SELECT
             waybill_id
            ,network_code
            ,'{{ execution_date | cst_ds }}' as input_date
            ,2 as flag
        FROM tmp_mid_04
) t1
LEFT JOIN (
    select
        *
    from jms_dim.dim_network_whole_massage
) t2
       ON t1.network_code = t2.code
)

--面单回收
, tmp_repeat AS(
select
       b.network_code
     , d.fran_code
     , d.agent_code
     , b.scantime_later as sender_date
     , d.network_name
     , d.fran_name
     , d.agent_name
     , a.bill_code
     , '面单回收' as exception_reason
     , 3 as flag
from (
         select bill_code --运单号
         from jms_dwd.dwd_elec_recycle_base_dt --电子面单回收表
         where dt between date_add('{{ execution_date | cst_ds }}', -60) and '{{ execution_date | cst_ds }}'
         and recycle_status in (1,2)
         group by bill_code
     ) a
         join
     (
         select billcode
              , network_code
              , scantime_later
         from (
                  select waybill_no                                                           as billcode       --运单号
                       , scan_site_code                                                       as network_code   --扫描网点编号
                       , substr(scan_time, 1, 10)                                             as scantime_later --扫描时间
                       , row_number() over (partition by waybill_no order by scan_time desc ) as rm
                  from jms_dwd.dwd_tab_barscan_taking_base_dt --揽收表
                  where dt = '{{ execution_date | cst_ds }}'
              ) t
         where rm = 1
     ) b
     on b.billcode = a.bill_code
         left join
     (
        select code                 --网点编码
             , name as network_name --网点名称
             , agent_code           --代理区编码
             , agent_name           --代理区名称
             , fran_code            --加盟商编码
             , fran_name            --加盟商名称
        from jms_dim.dim_network_whole_massage
     ) d
     on d.code = b.network_code
)

--结果表
insert overwrite table jms_dm.dm_waybill_exception_detail_dt partition (dt='{{ execution_date | cst_ds }}')
select * from (
select
      network_code                      --网点编码
    , fran_code                         --加盟商编码
    , agent_code                        --代理区编码
    , input_date AS sender_date         --发件日期
    , network_name                      --网点名称
    , fran_name                         --加盟商名称
    , agent_name                        --代理区名称
    , bill_code                         --运单号
    , '02_00' as exception_reason    --异常原因
    , flag                              --异常标志
from tmp_result
union all
SELECT
      network_code
    , fran_code
    , agent_code
    , sender_date
    , network_name
    , fran_name
    , agent_name
    , bill_code
    , exception_reason
    , flag
from tmp_repeat
) tmp
distribute by 1
;
